const connection = require('../../sql');
const WebSocketManager = require('../../service/common/websocket-util');
const wss = WebSocketManager.getInstance();
class MessageService {
	// 创建消息
	async createMessage({ title, type, content, senderId, sendType, deptIds, userIds, createtype }) {
		const isPublished = 'F';
		const now = new Date();
		const deptIdsStr = deptIds && deptIds.length > 0 ? deptIds.join(',') : null;
		const userIdsStr = userIds && userIds.length > 0 ? userIds.join(',') : null;
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 插入 sys_message
			const insertMessageSql = `
			INSERT INTO sys_message (
			  type, title, content, sender_id, receiver_id, status, created_at, 
			  is_published, send_type, send_at, update_at, dept_ids, user_ids, updata_id, del_flag, create_type
			)
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'F', ?)
		  `;
			const receiverId = sendType === 1015002 && userIds && userIds.length === 1 ? userIds[0] : null;
			const [messageResult] = await conn.execute(insertMessageSql, [
				type,
				title,
				content,
				senderId,
				receiverId,
				1014002,
				now,
				isPublished,
				sendType,
				now,
				now,
				deptIdsStr,
				userIdsStr,
				senderId,
				createtype,
			]);
			const messageId = messageResult.insertId;

			// 处理接收者
			let targetUserIds = [];

			if (sendType === 1015001) {
				const [users] = await conn.execute('SELECT id FROM sys_user WHERE del_flag = "F"');
				targetUserIds = users.map((user) => user.id);
			} else if (sendType === 1015002) {
				targetUserIds = userIds || [];
			} else if (sendType === 1015003) {
				if (deptIds && deptIds.length > 0) {
					const placeholders = deptIds.map(() => '?').join(',');
					const [users] = await conn.execute(
						`SELECT DISTINCT u.id 
				 FROM sys_user u 
				 JOIN sys_dept d ON u.id IN (
				   SELECT user_id FROM sys_user_dept WHERE dept_id IN (${placeholders})
				 )
				 WHERE u.del_flag = 'F' AND d.del_flag = 'F'`,
						deptIds
					);
					targetUserIds = users.map((user) => user.id);
				}
			}

			// 插入 sys_message_user
			if (targetUserIds.length > 0 && (type === 1013001 || sendType !== 1015002 || targetUserIds.length > 1)) {
				const values = targetUserIds.map((userId) => [messageId, userId, 1014002, null]);
				const insertUserMessageSql = `
			  INSERT INTO sys_message_user (message_id, user_id, status, read_at)
			  VALUES ?
			`;
				await conn.query(insertUserMessageSql, [values]);
			}

			await conn.commit();
			return { id: messageId };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	// 获取未读消息数
	async getUserUnreadCount(userId) {
		const conn = await connection.getConnection();

		try {
			const countSql = `
			SELECT (
			  SELECT COUNT(*) 
			  FROM sys_message m
			  JOIN sys_message_user mu ON m.id = mu.message_id
			  WHERE m.type = 1013001 
				AND m.del_flag = 'F' 
				AND mu.user_id = ? 
				AND mu.status = 1014002
			) + (
			  SELECT COUNT(*) 
			  FROM sys_message m
			  WHERE m.type IN (1013002, 1013003) 
				AND m.receiver_id = ? 
				AND m.status = 1014002 
				AND m.del_flag = 'F'
			) AS total
		  `;

			const [countResult] = await conn.execute(countSql, [userId, userId]);

			return {
				total: parseInt(countResult[0].total) || 0,
			};
		} catch (error) {
			console.error('Query error:', error);
			throw error;
		} finally {
			conn.release();
		}
	}
	// 更新公告发布状态
	async updatePublishStatus(id, isPublished, userId) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 验证消息类型并获取 title 和 content
			const [message] = await conn.execute(
				'SELECT type, send_type, receiver_id, dept_ids, user_ids, title, content FROM sys_message WHERE id = ? AND del_flag = "F"',
				[id]
			);
			if (!message.length || message[0].type !== 1013001) {
				throw new Error('只能修改公告消息的发布状态');
			}

			// 更新发布状态和发布时间
			const now = new Date();
			const updateSql = `
			UPDATE sys_message
			SET is_published = ?, send_at = ?, update_at = ?, updata_id = ?
			WHERE id = ? AND del_flag = 'F'
		  `;
			const [updateResult] = await conn.execute(updateSql, [isPublished, isPublished === 'T' ? now : null, now, userId, id]);
			if (updateResult.affectedRows === 0) {
				throw new Error('消息不存在或已被删除');
			}

			// 如果发布（isPublished='T'），处理推送
			if (isPublished === 'T') {
				let targetUserIds = [];
				const { send_type, receiver_id, dept_ids, user_ids, title, content } = message[0];

				if (send_type === 1015001) {
					// 全部用户
					const [users] = await conn.execute('SELECT id FROM sys_user WHERE del_flag = "F"');
					targetUserIds = users.map((user) => user.id);
				} else if (send_type === 1015002) {
					// 指定用户
					if (receiver_id) {
						targetUserIds = [receiver_id];
					} else if (user_ids) {
						targetUserIds = user_ids.split(',').map((id) => parseInt(id));
					}
				} else if (send_type === 1015003) {
					// 部门用户
					if (dept_ids) {
						const deptIds = dept_ids.split(',').map((id) => parseInt(id));
						const placeholders = deptIds.map(() => '?').join(',');
						const [users] = await conn.execute(
							`SELECT DISTINCT u.id 
				   FROM sys_user u 
				   JOIN sys_dept d ON u.id IN (
					 SELECT user_id FROM sys_user_dept WHERE dept_id IN (${placeholders})
				   )
				   WHERE u.del_flag = 'F' AND d.del_flag = 'F'`,
							deptIds
						);
						targetUserIds = users.map((user) => user.id);
					}
				}

				// 更新 sys_message_user
				if (targetUserIds.length > 0) {
					// 先删除旧记录
					await conn.execute('DELETE FROM sys_message_user WHERE message_id = ?', [id]);

					// 插入新记录
					const values = targetUserIds.map((userId) => [id, userId, 1014002, null]);
					const insertUserMessageSql = `
				INSERT INTO sys_message_user (message_id, user_id, status, read_at)
				VALUES ?
			  `;
					await conn.query(insertUserMessageSql, [values]);

					// 推送消息，包含 title 和 content
					const pushData = {
						message: '有新的消息通知，请查看！',
						messageId: id,
						title,
						content,
					};
					wss.sendToGroup(targetUserIds, pushData);
				}
			}

			await conn.commit();
			return { affectedRows: updateResult.affectedRows };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}
	// 获取消息列表
	async listMessages({ page, limit, title, type, isPublished }) {
		const offset = (page - 1) * limit;
		let whereClause = 'WHERE m.del_flag = "F" AND m.create_type = 1'; // 只查询人工创建
		const params = [];

		// 动态构建查询条件
		if (title) {
			whereClause += ' AND m.title LIKE ?';
			params.push(`%${title}%`);
		}
		if (type) {
			whereClause += ' AND m.type = ?';
			params.push(type);
		}
		if (isPublished) {
			whereClause += ' AND m.is_published = ?';
			params.push(isPublished);
		}

		const conn = await connection.getConnection();
		try {
			// 查询总数
			const countSql = `
			SELECT COUNT(*) as count 
			FROM sys_message m 
			${whereClause}
		  `;
			const [countResult] = await conn.execute(countSql, params);
			const total = countResult[0].count;

			// 查询消息列表
			const listSql = `SELECT
  m.id,
  m.type,
  m.title,
  m.content,
  m.receiver_id AS receiverId,
  m.status,
  m.related_type AS relatedType,
  m.related_id AS relatedId,
  m.created_at AS createdAt,
  m.read_at AS readAt,
  m.path,
  m.is_published AS isPublished,
  m.send_type AS sendType,
  m.send_at AS sendAt,
  m.update_at AS updateAt,
  m.dept_ids AS deptIds,
  m.user_ids AS userIds,
  m.create_type AS createType 
FROM
  sys_message m ${whereClause} 
ORDER BY
  m.created_at DESC 
  LIMIT ? OFFSET ?
		  `;
			const [rows] = await conn.execute(listSql, [...params, String(limit), String(offset)]);

			// 获取所有相关的部门和用户信息
			const deptIdsSet = new Set();
			const userIdsSet = new Set();
			rows.forEach((row) => {
				if (row.deptIds) row.deptIds.split(',').forEach((id) => deptIdsSet.add(id));
				if (row.userIds) row.userIds.split(',').forEach((id) => userIdsSet.add(id));
				if (row.receiverId) userIdsSet.add(row.receiverId.toString());
			});

			// 查询部门信息
			let deptMap = new Map();
			if (deptIdsSet.size > 0) {
				const deptPlaceholders = Array.from(deptIdsSet)
					.map(() => '?')
					.join(',');
				const [depts] = await conn.execute(
					`SELECT id, name FROM sys_dept WHERE id IN (${deptPlaceholders}) AND del_flag = 'F'`,
					Array.from(deptIdsSet)
				);
				deptMap = new Map(depts.map((d) => [d.id.toString(), d.name]));
			}

			// 查询用户信息
			let userMap = new Map();
			if (userIdsSet.size > 0) {
				const userPlaceholders = Array.from(userIdsSet)
					.map(() => '?')
					.join(',');
				const [users] = await conn.execute(
					`SELECT id, username FROM sys_user WHERE id IN (${userPlaceholders}) AND del_flag = 'F'`,
					Array.from(userIdsSet)
				);
				userMap = new Map(users.map((u) => [u.id.toString(), u.username]));
			}

			// 格式化返回数据
			const formattedRows = rows.map((row) => ({
				id: row.id,
				type: row.type,
				title: row.title,
				content: row.content,
				receiverId: row.receiverId,
				status: row.status,
				relatedType: row.relatedType,
				relatedId: row.relatedId,
				createdAt: row.createdAt,
				readAt: row.readAt,
				path: row.path,
				isPublished: row.isPublished,
				sendType: row.sendType,
				sendAt: row.sendAt,
				updateAt: row.updateAt,
				deptIds: row.deptIds ? row.deptIds.split(',').map((id) => +id) : [],
				deptInfo: row.deptIds
					? row.deptIds
							.split(',')
							.map((id) => `${id}:${deptMap.get(id) || '未知部门'}`)
							.join(',')
					: '',
				userIds: row.userIds ? row.userIds.split(',').map((id) => +id) : row.receiverId ? [row.receiverId] : [],
				userInfo: row.userIds
					? row.userIds
							.split(',')
							.map((id) => `${id}:${userMap.get(id) || '未知用户'}`)
							.join(',')
					: row.receiverId
					? `${row.receiverId}:${userMap.get(row.receiverId.toString()) || '未知用户'}`
					: '',
				createType: row.createType,
			}));

			return {
				current: page,
				limit,
				totalPage: Math.ceil(total / limit),
				total,
				rows: formattedRows,
			};
		} finally {
			conn.release();
		}
	}

	async updateMessage({ id, title, type, content, senderId, sendType, deptIds, userIds, createtype }) {
		const isPublished = type === 1013001 ? 'F' : 'T';
		const now = new Date();
		const deptIdsStr = deptIds && deptIds.length > 0 ? deptIds.join(',') : null;
		const userIdsStr = userIds && userIds.length > 0 ? userIds.join(',') : null;
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 更新 sys_message
			const updateMessageSql = `
			UPDATE sys_message
			SET 
			  type = ?, title = ?, content = ?, receiver_id = ?, 
			  is_published = ?, send_type = ?, update_at = ?, 
			  dept_ids = ?, user_ids = ?, updata_id = ?, create_type = ?
			WHERE id = ? AND del_flag = 'F'
		  `;
			const receiverId = sendType === 1015002 && userIds.length === 1 ? userIds[0] : null;
			const [updateResult] = await conn.execute(updateMessageSql, [
				type,
				title,
				content,
				receiverId,
				isPublished,
				sendType,
				now,
				deptIdsStr,
				userIdsStr,
				senderId,
				createtype,
				id,
			]);

			if (updateResult.affectedRows === 0) {
				throw new Error('消息不存在或已被删除');
			}

			// 删除旧的 sys_message_user 记录
			await conn.execute('DELETE FROM sys_message_user WHERE message_id = ?', [id]);

			// 根据 sendType 处理新的接收者
			let targetUserIds = [];
			if (sendType === 1015001) {
				const [users] = await conn.execute('SELECT id FROM sys_user WHERE del_flag = "F"');
				targetUserIds = users.map((user) => user.id);
			} else if (sendType === 1015002) {
				targetUserIds = userIds;
			} else if (sendType === 1015003) {
				const placeholders = deptIds.map(() => '?').join(',');
				const [users] = await conn.execute(
					`SELECT DISTINCT u.id 
			   FROM sys_user u 
			   JOIN sys_dept d ON u.id IN (
				 SELECT user_id FROM sys_user_dept WHERE dept_id IN (${placeholders})
			   )
			   WHERE u.del_flag = 'F' AND d.del_flag = 'F'`,
					deptIds
				);
				targetUserIds = users.map((user) => user.id);
			}

			// 为目标用户插入新的 sys_message_user 记录
			if (targetUserIds.length > 0 && (type === 1013001 || sendType !== 1015002 || targetUserIds.length > 1)) {
				const values = targetUserIds.map((userId) => [id, userId, 0, null]);
				const insertUserMessageSql = `
			  INSERT INTO sys_message_user (message_id, user_id, status, read_at)
			  VALUES ?
			`;
				await conn.query(insertUserMessageSql, [values]);
			}

			await conn.commit();
			return { id };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}
	async deleteMessage(ids) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 标记 sys_message 为删除
			const placeholders = ids.map(() => '?').join(',');
			const updateMessageSql = `
			UPDATE sys_message
			SET del_flag = 'T'
			WHERE id IN (${placeholders}) AND del_flag = 'F'
		  `;
			const [updateResult] = await conn.execute(updateMessageSql, ids);

			// 删除 sys_message_user 记录
			const deleteUserMessageSql = `
			DELETE FROM sys_message_user
			WHERE message_id IN (${placeholders})
		  `;
			await conn.execute(deleteUserMessageSql, ids);

			await conn.commit();
			return { affectedRows: updateResult.affectedRows };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async getMessageById(id) {
		const statement = `
      SELECT 
        id, type, title, content, sender_id AS senderId, receiver_id AS receiverId, 
        status, related_type AS relatedType, related_id AS relatedId, path, 
        created_at AS createdAt, read_at AS readAt, is_published AS isPublished
      FROM sys_message
      WHERE id = ? AND del_flag = "F" AND (type != 1 OR is_published = 'T')
    `;
		const [rows] = await connection.execute(statement, [id]);
		if (rows.length === 0) return null;
		return rows[0];
	}

	// 用户页面消息列表
	async getUserMessages(userId, { page, limit, type, title }) {
		const offset = (page - 1) * limit;
		let whereClause = '';
		const params = [];

		if (type) {
			whereClause += ' AND m.type = ?';
			params.push(type);
		}
		if (title) {
			whereClause += ' AND m.title LIKE ?';
			params.push(`%${title}%`);
		}

		const countSql = `
		  SELECT COUNT(*) as count
		  FROM sys_message m
		  LEFT JOIN sys_message_user mu ON m.id = mu.message_id AND mu.user_id = ?
		  WHERE m.del_flag = 'F'
			AND (
			  mu.user_id = ? OR m.receiver_id = ?
			)
			${whereClause}
		`;
		const listSql = `
		  SELECT 
			m.id, m.type, m.title, m.content, 
			m.sender_id AS senderId, m.receiver_id AS receiverId, 
			COALESCE(mu.status, m.status) AS status, 
			m.related_type AS relatedType, m.related_id AS relatedId, 
			m.created_at AS createdAt, 
			COALESCE(mu.read_at, m.read_at) AS readAt, 
			m.path, 
			m.del_flag AS delFlag, m.is_published AS isPublished, 
			m.send_type AS sendType, m.send_at AS sendAt, m.update_at AS updateAt, 
			m.dept_ids AS deptIds, m.user_ids AS userIds, 
			m.updata_id AS updataId, m.create_type AS createType
		  FROM sys_message m
		  LEFT JOIN sys_message_user mu ON m.id = mu.message_id AND mu.user_id = ?
		  WHERE m.del_flag = 'F'
			AND (
			  mu.user_id = ? OR m.receiver_id = ?
			)
			${whereClause}
		  ORDER BY m.created_at DESC
		  LIMIT ? OFFSET ?
		`;

		const conn = await connection.getConnection();
		try {
			const [countResult] = await conn.execute(countSql, [userId, userId, userId, ...params]);
			const [rows] = await conn.execute(listSql, [userId, userId, userId, ...params, String(limit), String(offset)]);

			return {
				current: page,
				limit,
				totalPage: Math.ceil(countResult[0].count / limit) || 1,
				total: countResult[0].count,
				rows,
			};
		} catch (error) {
			throw error;
		} finally {
			conn.release();
		}
	}

	async updateMessageReadStatus(userId, messages) {
		const conn = await connection.getConnection();
		try {
			await conn.beginTransaction();
			for (const { id, type, status } of messages) {
				if (![1013001, 1013002, 1013003].includes(type) || ![1014001, 1014002].includes(status)) {
					throw new Error(`无效的消息类型或状态: id=${id}`);
				}
				const newStatus = parseInt(status);
				const readAt = newStatus === 1014001 ? new Date() : null;

				if (type == 1013001) {
					const sql = `
				UPDATE sys_message_user mu
				JOIN sys_message m ON mu.message_id = m.id
				SET mu.status = ?, mu.read_at = ?
				WHERE mu.message_id = ? 
				  AND mu.user_id = ? 
				  AND m.type = 1013001 
				  AND m.del_flag = 'F'
				  AND (m.receiver_id = ? OR m.receiver_id IS NULL)
			  `;
					const [result] = await conn.execute(sql, [newStatus, readAt, id, userId, userId]);
					if (result.affectedRows === 0) {
						throw new Error(`公告消息不存在或无权限: id=${id}`);
					}
				} else {
					const sql = `
				UPDATE sys_message
				SET status = ?, read_at = ?
				WHERE id = ? 
				  AND receiver_id = ? 
				  AND type = ? 
				  AND del_flag = 'F'
			  `;
					const [result] = await conn.execute(sql, [newStatus, readAt, id, userId, type]);
					if (result.affectedRows === 0) {
						throw new Error(`个人消息不存在或无权限: id=${id}`);
					}
				}
			}
			await conn.commit();
			return { success: true };
		} catch (error) {
			await conn.rollback();
			throw error;
		} finally {
			conn.release();
		}
	}

	async deleteUserMessage(userId, ids) {
		const conn = await connection.getConnection();

		try {
			await conn.beginTransaction();

			// 查询消息类型和权限
			const querySql = `
			SELECT m.id, m.type, m.receiver_id
			FROM sys_message m
			WHERE m.id IN (${ids.map(() => '?').join(',')})
			  AND m.del_flag = 'F'
		  `;
			const [messages] = await conn.execute(querySql, ids);

			if (messages.length === 0) {
				throw new Error('没有可删除的消息');
			}

			// 分组处理：公告和个人消息
			const announcementIds = [];
			const personalMessageIds = [];

			for (const msg of messages) {
				if (msg.type === 1013001) {
					// 公告：验证 receiver_id 或 sys_message_user
					announcementIds.push(msg.id);
				} else if (msg.type === 1013002 || msg.type === 1013003) {
					// 个人消息：验证 receiver_id
					if (msg.receiver_id !== userId) {
						throw new Error(`无权限删除消息 ID=${msg.id}`);
					}
					personalMessageIds.push(msg.id);
				} else {
					throw new Error(`不支持的消息类型 ID=${msg.id}`);
				}
			}

			// 删除公告消息（sys_message_user）
			if (announcementIds.length > 0) {
				const deleteUserSql = `
			  DELETE mu
			  FROM sys_message_user mu
			  JOIN sys_message m ON mu.message_id = m.id
			  WHERE mu.message_id IN (${announcementIds.map(() => '?').join(',')})
				AND mu.user_id = ?
				AND m.type = 1013001
				AND m.del_flag = 'F'
				AND (m.receiver_id = ? OR m.receiver_id IS NULL)
			`;
				const [result] = await conn.execute(deleteUserSql, [...announcementIds, userId, userId]);
				if (result.affectedRows === 0) {
					throw new Error('公告消息不存在、未分配给用户或无权限');
				}
			}

			// 删除个人消息（sys_message.del_flag = 'T')
			if (personalMessageIds.length > 0) {
				const deleteMessageSql = `
			  UPDATE sys_message
			  SET del_flag = 'T'
			  WHERE id IN (${personalMessageIds.map(() => '?').join(',')})
				AND receiver_id = ?
				AND type IN (1013002, 1013003)
				AND del_flag = 'F'
			`;
				const [result] = await conn.execute(deleteMessageSql, [...personalMessageIds, userId]);
				if (result.affectedRows === 0) {
					throw new Error('个人消息不存在或无权限');
				}
			}

			await conn.commit();
			return { success: true };
		} catch (error) {
			await conn.rollback();
			console.error('Delete messages error:', error);
			throw error;
		} finally {
			conn.release();
		}
	}
}

module.exports = new MessageService();
